package com.test.controller.filterandsearch;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.test.bean.FilterAndSearchData;
import com.test.bean.FilterData;
import com.test.service.IFilterAndSearchService;
import com.test.util.ConvertUtil;
import com.test.util.CreateElementUtil;
import org.apache.ibatis.builder.xml.XMLMapperBuilder;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.FileSystemResource;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.*;

@RestController
@RequestMapping("/filterandsearch")
@SuppressWarnings("all")
public class FilterAndSearchController {

    private static Logger logger = LoggerFactory.getLogger(FilterAndSearchController.class);

    @Autowired
    private IFilterAndSearchService filterAndSearchService;
    @Autowired
    SqlSessionFactoryBean sqlSessionFactory;

    @RequestMapping(value = "/saveFilterData", method = RequestMethod.POST, produces="application/json;charset=UTF-8")
    @ResponseBody
    private Object saveFilterData(HttpServletRequest request, HttpServletResponse response, @RequestBody JSONObject obj) {
        String jsonString=obj.toJSONString();
        //解析json数据
        JSONObject json = JSON.parseObject(jsonString);
        String createArr=json.getString("createArr");
        String modifyArr=json.getString("modifyArr");

        Integer maxRow = ConvertUtil.converToInteger(ConvertUtil.converToString(json.getString("maxRow")));
        String listColumns = ConvertUtil.converToString(json.getString("listColumns"));
        String detailColumns = ConvertUtil.converToString(json.getString("detailColumns"));
        String tableName = ConvertUtil.converToString(json.getString("tableName"));
        String orderBy = ConvertUtil.converToString(json.getString("orderBy"));
        if("".equals(listColumns) || "".equals(detailColumns) || "".equals(tableName)) {
            Map result = new HashMap();
            result.put("success", 0);
            result.put("errMsg", "表基本信息不足");
            return result;
        }
        List<FilterData> allData = new ArrayList<FilterData>();
        if(null != maxRow) {
            for(int i = 1; i <= maxRow; i ++) {
                //checkbox是否勾选
                String row_1 = ConvertUtil.converToString(json.getString("row_" + i));
                if("".equals(row_1)) {
                    continue;
                }
                FilterData data = new FilterData();
                //data.setRow(i);
                data.setColumn(ConvertUtil.converToString(json.getString("row_" + i + "_column")));
                data.setOperate(ConvertUtil.converToString(json.getString("row_" + i + "_operate")));
                data.setValue(ConvertUtil.converToString(json.getString("row_" + i + "_value")));
                data.setLink(ConvertUtil.converToString(json.getString("row_" + i + "_link")));
                data.setSpace(ConvertUtil.converToInteger(json.getString("row_" + i + "_1")));
                allData.add(data);
            }
        }
        if(0 == allData.size()) {
            logger.info("没有选择过滤条件");
        }
        //添加上一行下一行数据参数
        for(int i = 0; i < allData.size(); i ++) {
            FilterData data = allData.get(i);
            data.setRow(i);
            if(i == 0) {
                data.setLastRow(-1);
                data.setNextRow(1);
            }else if(i == allData.size() - 1) {
                data.setLastRow(i - 1);
                data.setNextRow(-1);
            }else {
                data.setLastRow(i - 1);
                data.setNextRow(i + 1);
            }
        }
        FilterAndSearchData data = new FilterAndSearchData();
        data.setTableName(tableName);
        data.setListColumns(listColumns);
        data.setDetailColumns(detailColumns);
        data.setFilterStr(jsonString);//查询条件后面会保存到数据库
        data.setOrderBy(orderBy);
        return filterAndSearchService.dealFilterAndSearchData(allData, data);

    }

    /**
     * 使用json 的格式传入数组，不只传入map
     * @param request
     * @param response
     * @param obj
     * @return
     */
    @RequestMapping(value = "/saveFilterData02", method = RequestMethod.POST, produces="application/json;charset=UTF-8")
    @ResponseBody
    private Object saveFilterData02(HttpServletRequest request, HttpServletResponse response, @RequestBody JSONObject obj) {
        String jsonString=obj.toJSONString();
        //解析json数据
        JSONObject json = JSON.parseObject(jsonString);
        //Integer maxRow = ConvertUtil.converToInteger(ConvertUtil.converToString(json.getString("maxRow")));
        String listColumns = ConvertUtil.converToString(json.getString("listColumns"));
        String detailColumns = ConvertUtil.converToString(json.getString("detailColumns"));
        String tableName = ConvertUtil.converToString(json.getString("tableName"));
        String orderBy = ConvertUtil.converToString(json.getString("orderBy"));
        //页面添加主键名称验证，必须选择主键字段名称
        String priMaryName = ConvertUtil.converToString(json.getString("primaryName"));
        if("".equals(listColumns) || "".equals(detailColumns) || "".equals(tableName)) {
            Map result = new HashMap();
            result.put("success", 0);
            result.put("errMsg", "表基本信息不足");
            return result;
        }
        if("".equals(priMaryName)) {
            Map result = new HashMap();
            result.put("success", 0);
            result.put("errMsg", "表主键信息未设置");
            return result;
        }
        List<FilterData> allData = new ArrayList<FilterData>();
        //获取筛选json
        String dataListStr = json.getString("data");
        JSONArray array = JSONArray.parseArray(dataListStr);
        //JSON.parseObject(JSONArray.parseArray(json.getString("modifyArr")).get(0).toString()).getString("id")
        if(null != array && 0 < array.size()) {
            for(int i = 0; i < array.size(); i ++) {
                JSONObject object = JSON.parseObject(array.get(i).toString());
                String row_1 = ConvertUtil.converToString(object.getString("row"));//checkbox 状态
                if("".equals(row_1)) {
                    continue;
                }
                FilterData data = new FilterData();
                data.setColumn(ConvertUtil.converToString(object.getString("column")));
                data.setOperate(ConvertUtil.converToString(object.getString("operate")));
                data.setValue(ConvertUtil.converToString(object.getString("value")));
                data.setLink(ConvertUtil.converToString(object.getString("link")));
                data.setSpace(ConvertUtil.converToInteger(object.getString("row_1")));//缩进值
                allData.add(data);
            }
        }
        if(0 == allData.size()) {
            logger.info("没有选择过滤条件");
        }
        //添加上一行下一行数据参数
        for(int i = 0; i < allData.size(); i ++) {
            FilterData data = allData.get(i);
            data.setRow(i);
            if(i == 0) {
                data.setLastRow(-1);
                data.setNextRow(1);
            }else if(i == allData.size() - 1) {
                data.setLastRow(i - 1);
                data.setNextRow(-1);
            }else {
                data.setLastRow(i - 1);
                data.setNextRow(i + 1);
            }
        }
        FilterAndSearchData data = new FilterAndSearchData();
        data.setTableName(tableName);
        data.setListColumns(listColumns);
        data.setDetailColumns(detailColumns);
        data.setFilterStr(jsonString);//查询条件后面会保存到数据库
        data.setOrderBy(orderBy);
        data.setPrimaryName(priMaryName);
        return filterAndSearchService.dealFilterAndSearchData(allData, data);

    }


    @RequestMapping(value = "/queryAllTableNames", method = RequestMethod.GET, produces="application/json;charset=UTF-8")
    private List queryAllTableNames(HttpServletRequest request) {
        //获取配置的数据库
        String schema = "";
        try {
            Properties properties = new Properties();
            // 使用ClassLoader加载properties配置文件生成对应的输入流
            InputStream in = FilterAndSearchController.class.getClassLoader().getResourceAsStream("jdbc.properties");
            // 使用properties对象加载输入流
            properties.load(in);
            //获取key对应的value值
            String str = properties.getProperty("jdbc.url");
            str = str.replace("//", "");
            schema = str.substring(str.indexOf("/") + 1, str.indexOf("?"));
        }catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        if(null == schema || "".equals(schema)) {
            logger.info("获取mysql数据库名称失败");
            return null;
        }
        Map param = new HashMap();
        param.put("schema", schema);
        List result = filterAndSearchService.getAllTableName(param);
        return result;
    }

    /**
     * 根据表名查询数据库字段信息
     * @param request
     * @return
     */
    @RequestMapping(value = "/queryTableColumns", method = RequestMethod.GET, produces="application/json;charset=UTF-8")
    @ResponseBody
    private Object queryTableColumns(HttpServletRequest request) {
        String tableName = request.getParameter("tableName");
        String tableOwner = request.getParameter("tableOwner");
        String schemaName = request.getParameter("schemaName");
        if(null == tableName || "".equals(tableName)) {
            Map result = new HashMap();
            result.put("success", 0);
            result.put("errMsg", "表名不能为空");
            return result;
        }
        if(null == tableOwner || "".equals(tableOwner)) {
            tableOwner = "cigdi";
        }
        if(null == schemaName || "".equals(schemaName)) {
            schemaName = "public";
        }
        Map param = new HashMap();
        param.put("tableName", tableName);
        param.put("tableOwner", tableOwner);
        param.put("schemaName", schemaName);
        return filterAndSearchService.queryTableColumns(param);
    }

    /**
     * 查询数据列表
     * @param request
     * 传入参数格式为：(1表示勾选 0表示不勾选)
     * @return
     */
    /*
    {
    "column": [
    {"columnName": "id", "isListEvaluate": "1", "isDetailEvaluate":"1"},
    {"columnName": "name", "isListEvaluate": "1", "isDetailEvaluate":"0"} ],

    "filter": {
    "type": "binary_expr",
    "operator": "and",
    "left": {
    "type": "binary_expr",
    "operator": "=",
    "left" :{"type": "column_ref", "table": null, "column": "username"},
    "right":{"type": "string", "value": "admin"}
    },
    "right": {
    "type": "binary_expr",
    "operator": "and",
    "left": {"type": "column_ref", "table": null, "column": "id"},
    "right": {"type":null, "value": null}
    },
    "parentheses": true
    }
    }
     */
    @RequestMapping(value = "/queryTableList", method = RequestMethod.POST, produces="application/json;charset=UTF-8")
    @ResponseBody
    private Object queryTableList(HttpServletRequest request, HttpServletResponse response, @RequestBody JSONObject obj) {
        Map returnResult = new HashMap();
        String data=obj.toJSONString();
        //解析json数据
        JSONObject json = JSON.parseObject(data);
        String fid=json.getString("fid");//筛选列表查询数据id
        if(null == fid || "".equals(fid)) {
            returnResult.put("success", 0);
            returnResult.put("errMsg", "查询id不能为空");
            return returnResult;
        }
        FilterAndSearchData filterAndSearchData = filterAndSearchService.getFilterAndSearchById(fid);
        if(null == filterAndSearchData) {
            returnResult.put("success", 0);
            returnResult.put("errMsg", "根据id查询不到信息");
            return returnResult;
        }

        //解析xml 生成查询结果
        String resouce2 = CreateElementUtil.fileFolder + "\\" + fid + ".xml";
        String space = "namespace_" + fid;
        String queryId = "query" + fid;
        Map map = new HashMap();
        map.put("columns", filterAndSearchData.getListColumns());//列表查询字段
        SqlSession session = null;
        try {
            SqlSessionFactory sessionFactory = sqlSessionFactory.getObject();
            session = sessionFactory.openSession();
            Configuration config = session.getConfiguration();

            InputStream inputStream = new FileSystemResource(resouce2).getInputStream();//Resources.getResourceAsStream(resouce2);通過該方式可能找不到新增的文件
            XMLMapperBuilder mapperParser = new XMLMapperBuilder(inputStream, config, resouce2, config.getSqlFragments());
            Collection<String> a = config.getMappedStatementNames();
            if (!a.contains(space + "." + queryId)) {
                mapperParser.parse();
            }
            BoundSql boundSql = config.getMappedStatement(queryId).getBoundSql(map);
            List<Object> objectList = session.selectList(queryId, map);
            returnResult.put("success", 1);
            Map dataMap = new HashMap();
            if(null != objectList && 0 < objectList.size()) {
                dataMap.put("total", objectList.size());
                dataMap.put("rows", objectList);
            }else {
                dataMap.put("total", 0);
                dataMap.put("rows", new ArrayList());
            }
            returnResult.put("data", dataMap);
        }catch (Exception e) {
            logger.info("文件：" +  resouce2 + "查询出错",e );
            returnResult.put("success", 0);
            returnResult.put("errMsg", "文件：" +  resouce2 + "查询出错");
        }finally {
            if(null != session) {
                session.close();
            }
        }
        return returnResult;
    }

    /**
     * 查询数据详细 传入参数 fid : 保存的出查询筛选信息id      id：查询详细的内容
     * @param request
     * @return
     */
    @RequestMapping(value = "/queryTableDetail", method = RequestMethod.POST, produces="application/json;charset=UTF-8")
    @ResponseBody
    private Object queryTableDetail(HttpServletRequest request, HttpServletResponse response, @RequestBody JSONObject obj) {
        Map returnResult = new HashMap();
        String data=obj.toJSONString();
        //解析json数据
        JSONObject json = JSON.parseObject(data);
        String fid= json.getString("fid");//筛选列表查询数据id
        String id_data = json.getString("id");
        if(null == fid || "".equals(fid)) {
            returnResult.put("success", 0);
            returnResult.put("errMsg", "查询id不能为空");
            return returnResult;
        }
        if(null == id_data || "".equals(id_data)) {
            returnResult.put("success", 0);
            returnResult.put("errMsg", "详细id不能为空");
            return returnResult;
        }
        FilterAndSearchData filterAndSearchData = filterAndSearchService.getFilterAndSearchById(fid);
        if(null == filterAndSearchData) {
            returnResult.put("success", 0);
            returnResult.put("errMsg", "根据id查询不到信息");
            return returnResult;
        }

        //解析xml 生成查询结果
        String resouce2 = CreateElementUtil.fileFolder + "\\" + fid + ".xml";
        String space = "namespace_" + fid;
        String queryId = "detailquery" + fid;
        Map map = new HashMap();
        map.put("columns", filterAndSearchData.getDetailColumns());//详细查询字段
        map.put("id", id_data);
        SqlSession session = null;
        try {
            SqlSessionFactory sessionFactory = sqlSessionFactory.getObject();
            session = sessionFactory.openSession();
            Configuration config = session.getConfiguration();

            InputStream inputStream = new FileSystemResource(resouce2).getInputStream();//Resources.getResourceAsStream(resouce2);通過該方式可能找不到新增的文件
            XMLMapperBuilder mapperParser = new XMLMapperBuilder(inputStream, config, resouce2, config.getSqlFragments());
            Collection<String> a = config.getMappedStatementNames();
            if (!a.contains(space + "." + queryId)) {
                mapperParser.parse();
            }
            BoundSql boundSql = config.getMappedStatement(queryId).getBoundSql(map);
            List<Object> objectList = session.selectList(queryId, map);
            returnResult.put("success", 1);
            Map dataMap = new HashMap();
            if(null != objectList && 0 < objectList.size()) {
                dataMap.put("total", objectList.size());
                dataMap.put("rows", objectList);
            }else {
                dataMap.put("total", 0);
                dataMap.put("rows", new ArrayList());
            }
            returnResult.put("data", dataMap);
        }catch (Exception e) {
            logger.info("文件：" +  resouce2 + "查询出错",e );
            returnResult.put("success", 0);
            returnResult.put("errMsg", "文件：" +  resouce2 + "查询出错");
        }finally {
            if(null != session) {
                session.close();
            }
        }
        return returnResult;
    }

}
